package br.com.datasun.biblioteca.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.datasun.biblioteca.dao.factory.ConnectionFactory;
import br.com.datasun.biblioteca.entities.Editora;
import br.com.datasun.biblioteca.exceptions.DAOException;

public class EditoraDAO  {
	
	public List<Editora> listarEditorasCom(String nmEditora) throws DAOException{
		List<Editora> resultado = new ArrayList<Editora>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select id, nome from editora where nome like '?%'");
		try{
			//conecta ao banco de dados
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, nmEditora);
			//executa a consulta
			resultSet = preparedStatement.executeQuery();
			//gera os beans/dtos com o resultado da consulta
			while (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				Editora editora = new Editora();
				editora.setId(id);
				editora.setNome(nome);
				resultado.add(editora);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
	public boolean existeEditoraComONome(String nmEditora, long nmId) throws DAOException{
		
		boolean resultado = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select e.id, ");
		sql.append("       e.nome ");
		sql.append("  from editora e ");
		sql.append(" where nome like ?");
		sql.append("   and e.id <> ?"); //So Traz Registro se o id for diferente
		try{
			//conecta ao banco de dados
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, nmEditora);
			preparedStatement.setLong(2, nmId);
			//executa a consulta
			resultSet = preparedStatement.executeQuery();
			//gera os beans/dtos com o resultado da consulta
			if (resultSet.next()) {			
				resultado = true;
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
	public List<Editora> listarTodasEditoras() throws DAOException{
		List<Editora> resultado = new ArrayList<Editora>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select id, nome from editora");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				Editora editora = new Editora();
				editora.setId(id);
				editora.setNome(nome);
				resultado.add(editora);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
	public Editora getEditoraByID(Long idEditora) throws DAOException{
		Editora editora = null;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select id, nome from editora where id = ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, idEditora);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				editora = new Editora();
				editora.setId(id);
				editora.setNome(nome);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return editora;
	}
	
	public boolean atualizarEditora(Editora editora) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("update editora set nome = ? where id = ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, editora.getNome());
			preparedStatement.setLong(2, editora.getId());
			preparedStatement.executeUpdate();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}

	public Editora inserir(Editora editora) throws DAOException{
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet = null;
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into editora (nome) VALUES(?)");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, editora.getNome());
			preparedStatement.execute();
			resultSet = preparedStatement.getGeneratedKeys();

			if(resultSet.next()){
				Long idGerado = resultSet.getLong(1);
				editora.setId(idGerado);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return editora;
	}
	
	public boolean excluirEditoraPorId(Long idEditora) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sql = new StringBuilder();
		sql.append("delete from editora ");
		sql.append(" where id= ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, idEditora);
			preparedStatement.execute();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}

}
